package com.aplink.generic.database;

import java.util.ArrayList;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteQueryBuilder;
import android.text.TextUtils;

import com.aplink.generic.pojo.ContentData;
import com.aplink.generic.pojo.GenericAttribute;
import com.aplink.generic.pojo.GenericObject;
import com.aplink.generic.pojo.ObjPropInfo;
import com.aplink.generic.sharepreference.GenericSharePreference;
import com.aplink.generic.util.LogUtil;

public class CoreDatabaseHelper {
    private static final String DELETE_STATUS = "delete";
    private static final int GET_ALL = -1;
    private static final String INSERT_STATUS = "insert";
    private static final String NULL = null;
    private static final String UPDATE_STATUS = "update";
    private final SQLiteDatabase mDatabase;

    public CoreDatabaseHelper(final SQLiteDatabase database) {
        this.mDatabase = database;
    }

    private String buildWhereCondition(final ContentData objPropCol,
            final GenericObject genericObject) {
        final String table = objPropCol.getMainTable();
        final ObjPropInfo createdTime = objPropCol.getCreatedTime(table);
        final SqlMethods sqlMethods = SqlMethods.getInstance();
        String primaryKeyValues = null;
        if (createdTime != null) {
            primaryKeyValues = genericObject.getValue(createdTime
                    .getFullColumnName());
        }
        if ((createdTime == null) || TextUtils.isEmpty(primaryKeyValues)) {
            final ObjPropInfo primaryKey = objPropCol.getPrimaryKey(table);
            if (!TextUtils.isEmpty(genericObject.getValue(primaryKey
                    .getFullColumnName()))) {
                final String value = genericObject.getValue(
                        primaryKey.getFullColumnName()).toString();
                sqlMethods.equal(primaryKey, value);
            }
        } else {
            sqlMethods.equal(createdTime, primaryKeyValues);
        }
        return sqlMethods.toString();
    }

    public String deleteData(final ContentData objPropCol,
            final GenericObject genericObject) {
        final String mainTable = objPropCol.getMainTable();
        handleSyncStatus(objPropCol.getSyncStatus(mainTable), genericObject,
                CoreDatabaseHelper.DELETE_STATUS);
        final String whereClause = buildWhereCondition(objPropCol,
                genericObject);
        long newRowID = -1;
        final ContentValues contentValues = getContentValuesSyncStatus(
                objPropCol, genericObject);
        if (GenericSharePreference.getInstance().isOfflineMode()
                && (contentValues != null)) {
            newRowID = this.mDatabase.update(mainTable, contentValues,
                    whereClause, null);
        } else {
            newRowID = this.mDatabase.delete(mainTable, whereClause, null);
        }
        String realRowID = null;
        if (newRowID != -1) {
            final ObjPropInfo primaryKey = objPropCol.getPrimaryKey(mainTable);
            realRowID = genericObject.getValue(primaryKey.getFullColumnName())
                    .toString();
        }
        return realRowID;
    }

    private ContentValues getContentValues(final ContentData objPropCol,
            final GenericObject genericObject) {
        final ContentValues contentValues = new ContentValues();
        for (int i = 0; i < objPropCol.size(); i++) {
            final ObjPropInfo column = objPropCol.valueAt(i);
            if (column.isKey() && column.isAuto()) {
                continue;
            }
            if (!column.isSave()) {
                continue;
            }
            final String columnName = column.getColumnName();
            GenericAttribute attribute = genericObject.get(column
                    .getFullColumnName());
            if ((attribute == null) || (attribute.getValue() == null)) {
                if (column.isKey()) {
                    final ObjPropInfo createdTime = objPropCol
                            .getCreatedTime(genericObject.getTableName());
                    if (createdTime != null) {
                        attribute = genericObject.get(createdTime
                                .getFullColumnName());
                        if (attribute.getValue() != null) {
                            contentValues.put(columnName, attribute.getValue()
                                    .toString());
                        } else {
                            contentValues.putNull(columnName);
                        }
                    }
                } else if (column.isFKey()) {
                    attribute = genericObject.get(column
                            .getForeignFullColumnName());
                    if ((attribute == null) || (attribute.getValue() == null)) {
                        contentValues.putNull(columnName);
                    } else {
                        contentValues.put(columnName, attribute.getValue()
                                .toString());
                    }
                } else {
                    contentValues.putNull(columnName);
                }
            } else {
                contentValues.put(columnName, attribute.getValue().toString());
            }
        }
        return contentValues;
    }

    private ContentValues getContentValuesSyncStatus(
            final ContentData objPropCol, final GenericObject genericObject) {
        final ContentValues contentValues = new ContentValues();
        final ObjPropInfo column = objPropCol.getSyncStatus(objPropCol
                .getMainTable());
        if (column != null) {
            final String columnName = column.getColumnName();
            final GenericAttribute attribute = genericObject.get(column
                    .getFullColumnName());
            if ((attribute == null) || (attribute.getValue() == null)) {
                contentValues.putNull(columnName);
            } else {
                final String value = attribute.getValue().toString();
                contentValues.put(columnName, value);
            }
            return contentValues;
        }
        return null;
    }

    public ArrayList<GenericObject> getData(final ContentData objPropCol,
            String whereClause, final int limitRecord) {
        final ArrayList<GenericObject> genericObjectList = new ArrayList<GenericObject>();
        final String tableName = objPropCol.getMainTable();
        if (TextUtils.isEmpty(tableName)) {
            return genericObjectList;
        }
        final ArrayList<String> queriedColumns = objPropCol.getQueriedColumns();
        final ArrayList<ObjPropInfo> selectedColumn = objPropCol
                .getSelectedColumn();

        final StringBuilder syncStatus = new StringBuilder();
        for (int i = 0; i < objPropCol.getSyncStatus().size(); i++) {
            final ObjPropInfo info = objPropCol.getSyncStatus().get(i);
            if ((i != 0) && (i < objPropCol.getSyncStatus().size())) {
                syncStatus.append(" AND ");
            }
            syncStatus.append(String.format("%s is not 'delete'",
                    info.getFullColumnName()));
        }

        for (final String condition : objPropCol.getDataRules()) {
            if (TextUtils.isEmpty(whereClause)) {
                whereClause = condition;
            } else {
                whereClause += " AND " + condition;
            }
        }

        if (!syncStatus.toString().isEmpty()) {
            if (whereClause != null) {
                whereClause += " AND " + syncStatus.toString();
            } else {
                whereClause = syncStatus.toString();
            }
        }

        final String limit = limitRecord != CoreDatabaseHelper.GET_ALL ? String
                .valueOf(limitRecord) : null;
        String queryString;
        if (objPropCol.getJoins().isEmpty()) {
            queryString = SQLiteQueryBuilder.buildQueryString(false, tableName,
                    queriedColumns.toArray(new String[queriedColumns.size()]),
                    whereClause, objPropCol.getGroupBy(), null,
                    objPropCol.getOderBy(), limit);
        } else {
            queryString = SQLiteQueryBuilder.buildQueryString(false,
                    objPropCol.getJoins(),
                    queriedColumns.toArray(new String[queriedColumns.size()]),
                    whereClause, objPropCol.getGroupBy(), null,
                    objPropCol.getOderBy(), limit);
        }
        Cursor cursor = this.mDatabase.rawQuery(queryString, null);
        LogUtil.info(LogUtil.TAG_DATABASE, queryString, "buildQueryString",
                getClass());
        if (cursor != null) {
            cursor.moveToFirst();
            final int numRows = cursor.getCount();
            for (int i = 0; i < numRows; ++i) {
                final GenericObject genericObject = new GenericObject();
                genericObject.setTableName(tableName);
                for (int j = 0; j < selectedColumn.size(); j++) {
                    final ObjPropInfo objPropInfo = selectedColumn.get(j);
                    final String aliasColumnName = objPropInfo
                            .getAliasColumnName();
                    final int columnIndex = cursor
                            .getColumnIndex(aliasColumnName);
                    final String value = cursor.getString(columnIndex);
                    final GenericAttribute attribute = GenericAttribute.obtain(
                            value, objPropInfo);
                    genericObject.put(objPropInfo.getFullColumnName(),
                            attribute);
                }
                genericObjectList.add(genericObject);
                cursor.moveToNext();
            }
            cursor.close();
            cursor = null;
        }
        return genericObjectList;
    }

    public ArrayList<GenericObject> getData(final ContentData objPropCol,
            String whereClause, final int limitRecord,
            final boolean isCheckDataRule) {
        final ArrayList<GenericObject> genericObjectList = new ArrayList<GenericObject>();
        final String tableName = objPropCol.getMainTable();
        if (TextUtils.isEmpty(tableName)) {
            return genericObjectList;
        }
        final ArrayList<String> queriedColumns = objPropCol.getQueriedColumns();
        final ArrayList<ObjPropInfo> selectedColumn = objPropCol
                .getSelectedColumn();

        final StringBuilder syncStatus = new StringBuilder();
        for (int i = 0; i < objPropCol.getSyncStatus().size(); i++) {
            final ObjPropInfo info = objPropCol.getSyncStatus().get(i);
            if ((i != 0) && (i < objPropCol.getSyncStatus().size())) {
                syncStatus.append(" AND ");
            }
            syncStatus.append(String.format("%s is not 'delete'",
                    info.getFullColumnName()));
        }

        if (isCheckDataRule) {
            for (final String condition : objPropCol.getDataRules()) {
                if (TextUtils.isEmpty(whereClause)) {
                    whereClause = condition;
                } else {
                    whereClause += " AND " + condition;
                }
            }
        }

        if (!syncStatus.toString().isEmpty()) {
            if (whereClause != null) {
                whereClause += " AND " + syncStatus.toString();
            } else {
                whereClause = syncStatus.toString();
            }
        }

        final String limit = limitRecord != CoreDatabaseHelper.GET_ALL ? String
                .valueOf(limitRecord) : null;
        Cursor cursor;
        String queryString;
        if (objPropCol.getJoins().isEmpty()) {
            queryString = SQLiteQueryBuilder.buildQueryString(false, tableName,
                    queriedColumns.toArray(new String[queriedColumns.size()]),
                    whereClause, objPropCol.getGroupBy(), null,
                    objPropCol.getOderBy(), limit);
        } else {
            queryString = SQLiteQueryBuilder.buildQueryString(false,
                    objPropCol.getJoins(),
                    queriedColumns.toArray(new String[queriedColumns.size()]),
                    whereClause, objPropCol.getGroupBy(), null,
                    objPropCol.getOderBy(), limit);
        }
        cursor = this.mDatabase.rawQuery(queryString, null);
        LogUtil.info(LogUtil.TAG_DATABASE, queryString, "buildQueryString",
                getClass());
        if (cursor != null) {
            cursor.moveToFirst();
            final int numRows = cursor.getCount();
            for (int i = 0; i < numRows; ++i) {
                final GenericObject genericObject = new GenericObject();
                genericObject.setTableName(tableName);
                for (int j = 0; j < selectedColumn.size(); j++) {
                    final ObjPropInfo objPropInfo = selectedColumn.get(j);
                    final String aliasColumnName = objPropInfo
                            .getAliasColumnName();
                    final int columnIndex = cursor
                            .getColumnIndex(aliasColumnName);
                    final String value = cursor.getString(columnIndex);
                    final GenericAttribute attribute = GenericAttribute.obtain(
                            value, objPropInfo);
                    genericObject.put(objPropInfo.getFullColumnName(),
                            attribute);
                }
                genericObjectList.add(genericObject);
                cursor.moveToNext();
            }
            cursor.close();
            cursor = null;
        }
        return genericObjectList;
    }

    private void handleSyncStatus(final ObjPropInfo objPropInfo,
            final GenericObject genericObject, final String action) {
        if (objPropInfo != null) {
            final String columnName = objPropInfo.getFullColumnName();
            if (GenericSharePreference.getInstance().isOfflineMode()) {
                GenericSharePreference.getInstance().setDatabaseModified(true);
                final String syncStatusValue = genericObject
                        .getValue(columnName) == null ? null : genericObject
                        .getValue(columnName).toString();
                if (TextUtils.isEmpty(syncStatusValue)) {
                    genericObject.put(columnName, action);
                } else {
                    if (action
                            .equalsIgnoreCase(CoreDatabaseHelper.UPDATE_STATUS)) {
                        if (syncStatusValue
                                .equalsIgnoreCase(CoreDatabaseHelper.INSERT_STATUS)) {
                            genericObject.put(columnName,
                                    CoreDatabaseHelper.INSERT_STATUS);
                        } else {
                            genericObject.put(columnName,
                                    CoreDatabaseHelper.UPDATE_STATUS);
                        }
                    } else if (action
                            .equalsIgnoreCase(CoreDatabaseHelper.DELETE_STATUS)) {
                        genericObject.put(columnName,
                                CoreDatabaseHelper.DELETE_STATUS);
                    } else {
                        genericObject.put(columnName,
                                CoreDatabaseHelper.INSERT_STATUS);
                    }
                }
            } else {
                genericObject.put(columnName, CoreDatabaseHelper.NULL);
            }
        }
    }

    public String insertData(final ContentData objPropCol,
            final GenericObject genericObject) {
        final String mainTable = objPropCol.getMainTable();
        handleSyncStatus(objPropCol.getSyncStatus(mainTable), genericObject,
                CoreDatabaseHelper.INSERT_STATUS);

        // if (objPropCol.getPrimaryKey(mainTable).isAuto()) {
        // if (objPropCol.getCreatedTime(mainTable) != null) {
        //
        // ArrayList<GenericObject> lists = getData(objPropCol,
        // buildWhereCondition(objPropCol, genericObject), 1);
        // if (!lists.isEmpty()) {
        // String idValue = lists.get(0).getValue(
        // objPropCol.getPrimaryKey(mainTable));
        // genericObject.put(objPropCol.getPrimaryKey(mainTable)
        // .getFullColumnName(), idValue);
        // }
        // }
        // }

        final ContentValues contentValues = getContentValues(objPropCol,
                genericObject);
        final long newRowID = this.mDatabase.insertWithOnConflict(mainTable,
                null, contentValues, SQLiteDatabase.CONFLICT_REPLACE);
        String realRowID = null;
        if (newRowID != -1) {
            final ObjPropInfo primaryKey = objPropCol.getPrimaryKey(mainTable);
            if (primaryKey.isAuto()) {
                realRowID = String.valueOf(newRowID);
                genericObject.put(primaryKey.getFullColumnName(), realRowID);
            } else {
                GenericAttribute attribute = genericObject.get(primaryKey
                        .getFullColumnName());
                if ((attribute == null) || (attribute.getValue() == null)) {
                    final ObjPropInfo createdTime = objPropCol
                            .getCreatedTime(genericObject.getTableName());
                    if (createdTime != null) {
                        attribute = genericObject.get(createdTime
                                .getFullColumnName());
                        realRowID = attribute.getValue().toString();
                    }
                } else {
                    realRowID = genericObject.getValue(
                            primaryKey.getFullColumnName()).toString();
                }
            }
        }
        return realRowID;
    }

    public boolean isExists(final ContentData objPropCol,
            final String whereClause) {
        final SQLiteQueryBuilder queryBuilder = new SQLiteQueryBuilder();
        if (TextUtils.isEmpty(objPropCol.getJoins())) {
            queryBuilder.setTables(objPropCol.getMainTable());
        } else {
            queryBuilder.setTables(objPropCol.getJoins());
        }
        queryBuilder.appendWhere(whereClause);
        final Cursor cursor = queryBuilder.query(this.mDatabase, null, null,
                null, null, null, null, "1");
        return cursor.getCount() == 1;
    }

    public boolean isExists(final GenericObject genericObject,
            final ContentData objPropCol) {
        final String whereCondition = buildWhereCondition(objPropCol,
                genericObject);
        final SQLiteQueryBuilder queryBuilder = new SQLiteQueryBuilder();
        if (TextUtils.isEmpty(objPropCol.getJoins())) {
            queryBuilder.setTables(objPropCol.getMainTable());
        } else {
            queryBuilder.setTables(objPropCol.getJoins());
        }
        queryBuilder.appendWhere(whereCondition);
        final Cursor cursor = queryBuilder.query(this.mDatabase, null, null,
                null, null, null, null, "1");
        return cursor.getCount() == 1;
    }

    public String updateData(final ContentData objPropCol,
            final GenericObject genericObject) {
        final String mainTable = objPropCol.getMainTable();
        handleSyncStatus(objPropCol.getSyncStatus(mainTable), genericObject,
                CoreDatabaseHelper.UPDATE_STATUS);
        final ContentValues contentValues = getContentValues(objPropCol,
                genericObject);
        final String whereClause = buildWhereCondition(objPropCol,
                genericObject);
        final long newRowID = this.mDatabase.update(mainTable, contentValues,
                whereClause, null);
        String realRowID = null;
        if (newRowID != -1) {
            final ObjPropInfo primaryKey = objPropCol.getPrimaryKey(mainTable);
            realRowID = genericObject.getValue(primaryKey.getFullColumnName())
                    .toString();
        }
        return realRowID;
    }
}
